package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import modelo.Cd;

import conexao.ConnectionManager;

/**
 * @author senai
 */
class CdDAOImp implements CdDAO {

	private final String INSERT = "insert into cd (ehDuplo, titulo, ano, ehColetanea) values (?,?,?,?)";
	private final String UPDATE = "update cd set ehDuplo = ?, titulo = ?, ano = ?, ehColetanea = ? where idCd = ?";
	private final String DELETE = "delete from cd where idCd = ?";
	private final String ALL = "select idCd, ehDuplo, titulo, ano, ehColetanea from cd";
	private final String LIKE = "select idCd, ehDuplo, titulo, ano, ehColetanea from cd where titulo like ?";

	private ConnectionManager connectionManager;
	private static Connection connection;

	CdDAOImp() {
		this.openConnection();
	}
	

	
	public boolean isConnectionClose() {
		try {
			return connection.isClosed();
		} catch (SQLException ex) {
			return false;
		}
	}
	
	
	public void openConnection() {
		if ( connectionManager == null ) {
			connectionManager = ConnectionManager.getInstance();
		}
		
		if ( connection == null) {
			connection = connectionManager.getConnection();
		}
		
		if ( isConnectionClose() ) {
			connection = connectionManager.getConnection();
		}
	}


	public void closeConnection() {
		if ( connection != null && connectionManager != null) {
			if ( ! this.isConnectionClose() ) {
				connectionManager.closeConnection(connection);
			}
		}
	}


	
	public Cd salvar(Cd c) {
		try {
			PreparedStatement ps = null;
			//1 crie um instancia de PreparedStatement
			//para obter a chave gerada pelo banco
			ps = connection.prepareStatement(INSERT);
			
			ps.setObject(1, c.isEhDuplo());
			ps.setObject(2, c.getTitulo());
			ps.setObject(3, c.getAno());
			ps.setObject(4, c.isEhColetanea());
			
			ps.executeUpdate();
			
		} catch (SQLException ex) {
			Logger.getLogger(CdDAOImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
		return c;
	}
	

	public void alterar(Cd e) {
		PreparedStatement ps = null;
		try {
			//2 crie uma instancia de PreparedStatement 
			//passe para ela a instrucao para realizar alteracoes em uma registro do banco 
			ps = connection.prepareStatement(UPDATE);
						
			ps.setObject(1, e.isEhDuplo());
			ps.setObject(2, e.getTitulo());
			ps.setObject(3, e.getAno());
			ps.setObject(4, e.isEhColetanea());
			ps.setObject(5, e.getIdCd());
			
			ps.executeUpdate();

		} catch (SQLException ex) {
			Logger.getLogger(CdDAOImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
	}


	public void deletar(Cd e) {
		try {
			PreparedStatement ps = null;
			ps = connection.prepareStatement(DELETE);
			//3 passe para o PreparedStatement o valor do id que deve ser excluido do banco de dados 
			ps.setObject(1, e.getIdCd());
			
			ps.executeUpdate();

		} catch (SQLException ex) {
			Logger.getLogger(CdDAOImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
	}
	

	public List<Cd> findAll() {
		List<Cd> cds = new ArrayList<Cd>();

		try {
			PreparedStatement ps = null;
			ResultSet rs = null;
			ps = connection.prepareStatement(ALL);
			
			//4 Obtenha uma instancia de resultset
			rs = ps.executeQuery();
			
			while (rs.next()) {
				Cd cd = new Cd();
				cd.setIdCd(rs.getInt(1));
				cd.setEhDuplo(rs.getBoolean(2));
				cd.setTitulo(rs.getString(4));
				cd.setAno(rs.getInt(3));				
				cd.setEhColetanea(rs.getBoolean(5));
				cds.add(cd);
				//5 crie uma instancia de cidade com os dados
				//obtidos do banco e adicione a lista de cidades
				
			}

		} catch (SQLException ex) {
			Logger.getLogger(CdDAOImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
		return cds;
	}

	public List<Cd> buscaCdPorNome(Cd cd) {
		List<Cd> cds = new ArrayList<Cd>();
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = connection.prepareStatement(LIKE);
			//6 informe o parmetro para instrucao like
			ps.setObject(1, cd.getTitulo());
		
			rs = ps.executeQuery();

			while (rs.next()) {
				cds.add(new Cd(
						rs.getInt("idCd"),
						rs.getBoolean("ehDuplo"),
						rs.getString("titulo"),
						rs.getInt("ano"),
						rs.getBoolean("ehColetanea")));
			}

		} catch (SQLException ex) {
			Logger.getLogger(CdDAOImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
		return cds;
	}
	
	
}
